﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Today_ZhiChu]
	(
		@CompanyId int,
		@Today nchar(10)
	)
AS
Begin
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY c.Id ASC)AS Row
	,c.Id As CompanyId
	,c.CompanyName
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=20 and Convert(nchar(10),CreateDate,120)=@Today),0) As OAA--直接成本
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=21 and Convert(nchar(10),CreateDate,120)=@Today),0) As OAB--间接成本
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=8 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBA--市场费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=1 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBB--日常生活
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=5 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBC--行政办公
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=9 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBD--财务费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=10 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBE--人力费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=11 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBF--部门基金
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=7 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBG--总办费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=12 and Convert(nchar(10),CreateDate,120)=@Today),0) As OBH--其他费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=25 and Convert(nchar(10),CreateDate,120)=@Today),0) As OCA--投资
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=18 and Convert(nchar(10),CreateDate,120)=@Today),0) As OCB--借款
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=23 and Convert(nchar(10),CreateDate,120)=@Today),0) As ODA--有形资产
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=24 and Convert(nchar(10),CreateDate,120)=@Today),0) As ODB--无形资产
	From Company c Where (@CompanyId=0 Or Id=@CompanyId)
	)
	
	Select * From list,(SELECT SUM(OAA) AS OAA_a,SUM(OAB) AS OAB_a,SUM(OBA) AS OBA_a,SUM(OBB) AS OBB_a
	,SUM(OBC) AS OBC_a,SUM(OBD) AS OBD_a,SUM(OBE) AS OBE_a,SUM(OBF) AS OBF_a
	,SUM(OBG) AS OBG_a,SUM(OBH) AS OBH_a,SUM(OCA) AS OCA_a,SUM(OCB) AS OCB_a
	,SUM(ODA) AS ODA_a,SUM(ODB) AS ODB_a
	 FROM list) AS n
End
	RETURN
